# -*_ codeing=utf-8 -*-
# @Time: 2022/12/28 17:27
# @Author: foxhuty
# @File: to_sql.py
# @Software: PyCharm
# @Based on python 3.10

import pandas as pd
from sqlalchemy import create_engine


def read_into_mysql(file_path, tb_name, db_name='db_crawling_data'):
    engine = create_engine(f"mysql+mysqlconnector://root:foxmmer@localhost:3306/{db_name}?charset=utf8")
    conn = engine.connect()
    df = pd.read_csv(file_path, encoding='utf8')
    print(df.shape)
    df.to_sql(name=tb_name, con=conn, if_exists='append')
    conn.close()


def read_from_mysql(tb_name, db_name='db_crawling_data'):
    engine = create_engine(f'mysql+mysqlconnector://root:foxmmer@localhost/{db_name}')
    conn = engine.connect()
    data = pd.read_sql(sql=f"select * from {tb_name}", con=conn)
    # data.reset_index(inplace=True, drop=True)
    # print(data['index'].head())
    # data['index'] = [i + 1 for i in data.index]
    # data.rename(columns={'index': '序号'}, inplace=True)
    with pd.ExcelWriter(f'D:\\爬虫数据\\{tb_name}.xlsx') as writer:
        data.to_excel(writer, sheet_name=tb_name, index=False)
    conn.close()


if __name__ == '__main__':
    file = r'D:\PycharmProjects\EC_Dict\ECDICT-master\stardict.csv'
    read_into_mysql(file, 'tb_stardict')
    # read_from_mysql('countrylanguage',db_name='world')
